<?php

namespace app\admin\controller\admin;
use app\common\controller\Backend;
use think\Db;
use think\Loader;

/**
 * 用户管理
 *
 * @icon fa fa-file-text-o
 */
class User extends Backend
{
    /**
     * School模型对象
     */
    protected $searchFields = 'id,nick_name,phone,user_province';
    protected $model = null;
    protected $noNeedRight = ['cx_province_select','set_black','month_province','look_data','get_goods',
        'daily_active_province','user_list_cj','cancel_vip','look_data','user_list','area_list','getProvince','getCity','getRegion'];
//    protected $noNeedLogin = ['update_user'];
    protected $isSuperAdmin = false;
    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\admin\Distributor();
        //是否超级管理员
        $this->isSuperAdmin = $this->auth->isSuperAdmin();
    }

    /**
      * 会员明细表
     */
    public function user_list()
    {
        $request = $this->request->request();
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $where_new =[
                'u.is_cancel'=>0
            ];
            $distributor_type = isset($request['distributor_type'])?$request['distributor_type']:'';
            $begin_time = isset($request['begin_time'])?$request['begin_time']:'';
            $end_time = isset($request['end_time'])?$request['end_time']:'';
            $province_id = isset($request['province_id'])?$request['province_id']:0;
            $city_id = isset($request['city_id'])?$request['city_id']:0;
            $region_id = isset($request['region_id'])?$request['region_id']:0;
            $year = isset($request['year'])?$request['year']:'';
            $test_type = isset($request['test_type'])?$request['test_type']:'';
            $sex = isset($request['sex'])?$request['sex']:'';
            $crowd = isset($request['crowd'])?$request['crowd']:'';
            $school = isset($request['school'])?$request['school']:'';
            //    if ($distributor) $where_new['distributor_name'] = ['like','%'.$distributor.'%'];
            if ($distributor_type && $distributor_type!='undefined') $where_new['distributor_type'] = $distributor_type;
            if ($province_id >1) $where_new['u.province_id'] = $province_id;
            if ($city_id >0) $where_new['u.city_id'] = $city_id;
            if ($region_id >0) $where_new['u.region_id'] = $region_id;
            if ($year) $where_new['u.graduation'] = $year;
            if ($test_type) $where_new['u.test_type'] = $test_type;
            if ($crowd != '') $where_new['u.crowd'] = $crowd;
            if ($sex != '') $where_new['u.sex'] = $sex;
            if ($begin_time) $where_new['l.create_time'] = ['between',[$begin_time,$end_time]];
            if ($school) $where_new['u.middle_school'] = ["LIKE", "%{$school}%"];
            $count =  Db::name('zd_user')
                ->alias('u')
                ->join('app_card_log l','l.user_id = u.user_id','right')
                ->join('app_cd_admin ca','ca.id = l.distributor_id','left')
                ->where($where_new)
                ->where($where)
                ->count();
            $list =  Db::name('zd_user')
                ->alias('u')
                ->join('app_card_log l','l.user_id = u.user_id','right')
                ->join('app_cd_admin ca','ca.id = l.distributor_id','left')
                ->where($where_new)
                ->where($where)
                //  ->where($con)
                ->group('u.id')
                ->field("u.id,u.user_id,user_name,nick_name,u.province,u.city,u.region,u.province_id,u.city_id,u.region_id,middle_school,crowd,sex,graduation,test_type,reg_time,score1,clicks,logins")
                ->order('l.province_id desc')
                ->limit($offset, $limit)
                ->select();
            foreach ($list as $w=>&$v){
                $v['new_name'] = $v['province'].'-'.$v['city'].'-'.$v['region'];
            }
            $sql = Db::name('zd_user')->getLastSql();
            $result = array("total" => $count, "rows" => $list,'sql'=>$sql);
            return json($result);
        }
        return $this->view->fetch();
    }
    /**
     * 区域汇总总表i
     */
    public function area_list()
    {
        $request = $this->request->request();
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $where_new =[
                'u.is_cancel'=>0
            ];
            $distributor_type = isset($request['distributor_type'])?$request['distributor_type']:'';
            $begin_time = isset($request['begin_time'])?$request['begin_time']:'';
            $end_time = isset($request['end_time'])?$request['end_time']:'';
            $province_id = isset($request['province_id'])?$request['province_id']:0;
            $city_id = isset($request['city_id'])?$request['city_id']:0;
            $region_id = isset($request['region_id'])?$request['region_id']:0;
            $year = isset($request['year'])?$request['year']:'';
            //    if ($distributor) $where_new['distributor_name'] = ['like','%'.$distributor.'%'];
            if ($distributor_type) $where_new['distributor_type'] = $distributor_type;
            if ($province_id >1) $where_new['l.province_id'] = $province_id;
            if ($city_id >0) $where_new['l.city_id'] = $city_id;
            if ($region_id >0) $where_new['l.region_id'] = $region_id;
            if ($year) $where_new['u.graduation'] = $year;
            if ($begin_time) $where_new['l.create_time'] = ['between',[$begin_time,$end_time]];
            if($city_id == 0){ //省份
                $list =  Db::name('zd_user')
                    ->alias('u')
                    ->join('app_card_log l','l.user_id = u.user_id','right')
                    ->join('app_cd_admin ca','ca.id = l.distributor_id','left')
                    ->where($where_new)
                    ->where($where)
                    //  ->where($con)
                    ->group('l.province_id')
                    ->field("l.province_id,l.city_id,l.region_id,count(*) as num,sum(case when crowd=1 then 1 else 0 end) as student_num,
                sum(case when crowd=2 then 1 else 0 end) as parents_num,sum(case when crowd=0 then 1 else 0 end) as no_cer_num,
                sum(case when test_type='春季高考' then 1 else 0 end) as spring_num,sum(case when test_type='普通高考' then 1 else 0 end) as common_num, 
                sum(case when test_type='艺术类' then 1 else 0 end) as art_num,sum(case when test_type='体育类' then 1 else 0 end) as sports_num, 
                sum(case when test_type='特殊类' then 1 else 0 end) as special_num,sum(case when test_type='' then 1 else 0 end) as no_put_student_num,
                sum(case when sex=1 then 1 else 0 end) as boy_num,sum(case when sex=2 then 1 else 0 end) as gril_num,  sum(case when sex=0 then 1 else 0 end) as no_sex_num
                ")
                    ->order('l.province_id desc')
                    ->limit($offset, $limit)
                    ->select();
            }else if($city_id >0 && $region_id==0){   //城市
                $list =  Db::name('zd_user')
                    ->alias('u')
                    ->join('app_card_log l','l.user_id = u.user_id','right')
                    ->join('app_cd_admin ca','ca.id = l.distributor_id','left')
                    ->where($where_new)
                    ->where($where)
                    //  ->where($con)
                    ->group('l.city_id')
                    ->field("l.province_id,l.city_id,l.region_id,count(*) as num,sum(case when crowd=1 then 1 else 0 end) as student_num,
                sum(case when crowd=2 then 1 else 0 end) as parents_num,sum(case when crowd=0 then 1 else 0 end) as no_cer_num,
                sum(case when test_type='春季高考' then 1 else 0 end) as spring_num,sum(case when test_type='普通高考' then 1 else 0 end) as common_num, 
                sum(case when test_type='艺术类' then 1 else 0 end) as art_num,sum(case when test_type='体育类' then 1 else 0 end) as sports_num, 
                sum(case when test_type='特殊类' then 1 else 0 end) as special_num,sum(case when test_type='' then 1 else 0 end) as no_put_student_num,
                sum(case when sex=1 then 1 else 0 end) as boy_num,sum(case when sex=2 then 1 else 0 end) as gril_num,  sum(case when sex=0 then 1 else 0 end) as no_sex_num
                ")
                    ->order('l.city_id desc')
                    ->limit($offset, $limit)
                    ->select();
            }else if($city_id >0 && $region_id >0){ //地区
                $list =  Db::name('zd_user')
                    ->alias('u')
                    ->join('app_card_log l','l.user_id = u.user_id','right')
                    ->join('app_cd_admin ca','ca.id = l.distributor_id','left')
                    ->where($where_new)
                    ->where($where)
                    //  ->where($con)
                    ->group('l.region_id')
                    ->field("l.province_id,l.city_id,l.region_id,count(*) as num,sum(case when crowd=1 then 1 else 0 end) as student_num,
                sum(case when crowd=2 then 1 else 0 end) as parents_num,sum(case when crowd=0 then 1 else 0 end) as no_cer_num,
                sum(case when test_type='春季高考' then 1 else 0 end) as spring_num,sum(case when test_type='普通高考' then 1 else 0 end) as common_num, 
                sum(case when test_type='艺术类' then 1 else 0 end) as art_num,sum(case when test_type='体育类' then 1 else 0 end) as sports_num, 
                sum(case when test_type='特殊类' then 1 else 0 end) as special_num,sum(case when test_type='' then 1 else 0 end) as no_put_student_num,
                sum(case when sex=1 then 1 else 0 end) as boy_num,sum(case when sex=2 then 1 else 0 end) as gril_num,  sum(case when sex=0 then 1 else 0 end) as no_sex_num
                ")
                    ->order('l.region_id desc')
                    ->limit($offset, $limit)
                    ->select();
            }
            $sql =  Db::name('zd_user')->getLastSql();
            $time = time();
            foreach ($list as $w=>&$v){
                if($city_id == 0){ //省份
                    $v['new_name'] = get_province_name($v['province_id']);
                }else if($city_id >0 && $region_id==0){   //城市
                    $v['new_name'] = Db::name('province_city')->where(['id'=>$v['city_id']])->value('city');
                }else if($city_id >0 && $region_id >0){ //地区
                    $v['new_name'] = Db::name('province_city_region')->where(['id'=>$v['region_id']])->value('region');
                }
                $user_where = [];
                if ($begin_time) $user_where['reg_time'] = ['between',[$begin_time,$end_time]];
                if ($v['province_id'] >0) $user_where['province_id'] = $v['province_id'];
                if ($v['city_id'] >0 && $province_id >1) $user_where['city_id'] = $v['city_id'];
                if ($v['region_id'] >0 && $province_id >1) $user_where['region_id'] = $v['region_id'];
                if ($year) $user_where['graduation'] = $year;
                $v['begin_time'] =  $begin_time;
                $v['end_time'] =  $end_time;
                $v['graduation'] =  $year;
                $v['reg_num'] =  Db::name('zd_user')->where($user_where)->count();
                $v['zd_sql'] =  Db::name('zd_user')->getLastSql();
                $user_vip_where = [
                    'is_activate'=>1,
                    'is_out'=>0,
                ];
                if ($begin_time) {
                    $user_vip_where['period_time'] = ['>',$end_time];
                    $user_vip_where['effect_time'] = ['<',$end_time];
                }else{
                    $user_vip_where['period_time'] = ['>',$time];
                    $user_vip_where['effect_time'] = ['<',$time];
                }
                if ($v['province_id'] >0) $user_vip_where['province_id'] = $v['province_id'];
                if ($v['city_id'] >0 && $province_id >1) $user_vip_where['city_id'] = $v['city_id'];
                if ($v['region_id'] >0) $user_vip_where['region_id'] = $v['region_id'];
                if ($year) $user_vip_where['graduation'] = $year;
                $v['num'] =  Db::name('card_log')->where($user_vip_where)->count();
            }
            if($city_id == 0 && $province_id == 0){ //省份
                $array[0]['new_name']  = '全国';
            }else if($city_id == 0 && $province_id > 0){
                $array[0]['new_name'] = get_province_name($province_id);
            }else if($city_id >0 && $region_id==0){   //城市
                $array[0]['new_name'] = Db::name('province_city')->where(['id'=>$city_id])->value('province');
            }else if($city_id >0 && $region_id >0){ //地区
                $array[0]['new_name'] = Db::name('province_city_region')->where(['id'=>$region_id])->value('city');
            }

            $array[0]['begin_time'] =  $begin_time;
            $array[0]['end_time'] =  $end_time;
            $array[0]['graduation'] =  $year;
            $array[0]['num']=array_sum(array_map(function($val){return $val['num'];}, $list));
            $array[0]['student_num']=array_sum(array_map(function($val){return $val['student_num'];}, $list));
            $array[0]['parents_num']=array_sum(array_map(function($val){return $val['parents_num'];}, $list));
            $array[0]['no_cer_num']=array_sum(array_map(function($val){return $val['no_cer_num'];}, $list));
            $array[0]['spring_num']=array_sum(array_map(function($val){return $val['spring_num'];}, $list));
            // $array[0]['average_price']=array_sum(array_map(function($val){return $val['average_price'];}, $list));
            $array[0]['common_num']=array_sum(array_map(function($val){return $val['common_num'];}, $list));
            $array[0]['art_num']=array_sum(array_map(function($val){return $val['art_num'];}, $list));
            $array[0]['sports_num']=array_sum(array_map(function($val){return $val['sports_num'];}, $list));
            $array[0]['special_num']=array_sum(array_map(function($val){return $val['special_num'];}, $list));
            $array[0]['no_put_student_num']=array_sum(array_map(function($val){return $val['no_put_student_num'];}, $list));
            $array[0]['boy_num']=array_sum(array_map(function($val){return $val['boy_num'];}, $list));
            $array[0]['gril_num']=array_sum(array_map(function($val){return $val['gril_num'];}, $list));
            $array[0]['no_sex_num']=array_sum(array_map(function($val){return $val['no_sex_num'];}, $list));
            $array[0]['reg_num']=array_sum(array_map(function($val){return $val['reg_num'];}, $list));
            $data=array_merge($array,$list);
            $coins = array_column($data,'num');
            array_multisort($coins,SORT_DESC,$data);
            $result = array("total" => count($data), "rows" => $data,'sql'=>$sql);
            return json($result);
        }
        return $this->view->fetch();
    }
    /**
     * 获取省份
     */
    public function  getProvince(){
      $province_info =  Db::name('province')->where(['is_del'=>0])->field('id,name')->select();
      $data=[
          'province_info'=>$province_info
      ];
      $this->success('获取成功','',$data);
    }
    /**
     * 获取城市
     */
    public function  getCity(){
        $request = $this->request->request();
        $province_id = isset($request['province_id'])?$request['province_id']:'';
        $where =[
            'is_del'=>0
        ];
        if($province_id){
            $where['province_id'] = $province_id;
        }
        $city_info =  Db::name('province_city')
            ->where($where)
            ->field('id,city')->select();
        $new_arr =[['id'=>0,'city'=>'全部']];
        foreach ($city_info as $w=>$v){
            $new_arr[$w+1] = $city_info[$w];
        }
        $data=[
            'city_info'=>$new_arr
        ];
        $this->success('获取成功','',$data);
    }

    /**
     * 获取城市
     */
    public function  getRegion(){
        $request = $this->request->request();
        $province_id = isset($request['province_id'])?$request['province_id']:'';
        $city_id = isset($request['city_id'])?$request['city_id']:'';
        $where =[
            'is_del'=>0
        ];
        if($province_id){
            $where['province_id'] = $province_id;
        }
        if($city_id){
            $where['city_id'] = $city_id;
        }
        $region_info =  Db::name('province_city_region')
            ->where($where)
            ->field('id,city,region')->select();
        $new_arr =[['id'=>0,'region'=>'全部']];
        foreach ($region_info as $w=>$v){
            $new_arr[$w+1] = $region_info[$w];
        }
        $data=[
            'region_info'=>$new_arr
        ];
        $this->success('获取成功','',$data);
    }
    public function look_data(){  //会员资料
        $this->request->filter(['strip_tags']);
        $id=input('user_id');
        $province_id=input('province_id');
      //  var_dump($province_id);
      //  var_dump($id);
        $user=Db::name('zd_user')
            ->alias('a')
            ->where('a.user_id="'.$id.'"')
            ->field('a.*,IFNULL(b.order_num, 0) as order_num,IFNULL(b.total_money, 0) as total_money')
            ->join("(SELECT count(*) as order_num ,sum(payment_money) as total_money , user_id as order_user 
                FROM app_zd_order_master where order_status='1' AND is_del=0 AND order_id>40 group by user_id ) b",'a.user_id=b.order_user','left')
            ->find();

        //认证信息
        $verify_info = $this->verifyInfo($id);

        if ($this->request->isAjax()) {
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $type=input('type')?input('type'):0;
            if ($type==1){
                $count= DB::name('zd_user_vip')
                    ->alias('a')
                    ->field('a.*,b.user_name,b.nick_name,b.register_type')
                    ->join('zd_user b','a.user_id=b.user_id','left')
                    ->where("a.user_id='".$id."'")
                    ->count();
                $list= DB::name('zd_user_vip')
                    ->alias('a')
                    ->field('a.*,b.user_name,b.nick_name,b.register_type')
                    ->join('zd_user b','a.user_id=b.user_id','left')
                    ->where("a.user_id='".$id."'")
                    ->where($where)
                    ->order($sort, $order)
                    ->limit($offset,$limit)
                    ->select();
                //填报会员处理
                $tb_info=Db::connect("database_zy")->table('data_user_fs')
                    ->where(['user_id'=>$id,'is_del'=>0])
                    ->column('km,ck_fs','sf');
                foreach($list as $k => &$v){
                    if($v['vip_type']==3){
                        $province_name = Db::name('province')->where(['id'=>$v['province_id']])->value('name');
                        $v['tb_sf']=isset($tb_info[$province_name]['sf'])?$tb_info[$province_name]['sf']:'-';
                        $v['tb_km']=isset($tb_info[$province_name]['km'])?$tb_info[$province_name]['km']:'-';
                        $v['tb_ck_fs']=isset($tb_info[$province_name]['ck_fs'])?$tb_info[$province_name]['ck_fs']:'-';
                    }else{
                        $v['tb_sf']='-';
                        $v['tb_km']='-';
                        $v['tb_ck_fs']='-';
                    }
                }
                $result = array("total" =>$count, "rows" => $list);
                return json($result);
            }
            if ($type==2){
                $list= DB::name('zd_order_master')
                    ->alias('a')
                    ->field('a.*,b.goods_name,b.goods_type')
                    ->join('zd_order_detail b','a.order_sn = b.order_sn','left')
                    ->where("a.user_id='".$id."'")
                    ->where($where)
                    ->order($sort, $order)
                    ->paginate($limit);
                $result = array("total" => $list->total(), "rows" => $list->items());
                return json($result);
            }
            $list =  $list = $this->model
                ->where("user_id='".$id."'")
                ->where($where)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();
            $result = array("total" => 1, "rows" => $list);
            return json($result);
        }
        $tag_info = Db::name('zd_user_tag')->group('tag_id')->field('tag_id')->select();
        $vip_info = $this->get_vip($id);

        $this->assignconfig('id',$id);
        $this->assign('user',$user);
        $this->assign('verify_info',$verify_info);
        $this->assign('tag_info',$tag_info);
        $this->assign('vip_info',$vip_info);
        return $this->view->fetch();
    }

    //认证信息
    public function verifyInfo($id){
        $user= Db::name('zd_user')
            ->where(['user_id'=>$id,'is_cancel'=>0,'is_hacker'=>0])
            ->field('type,crowd,school,middle_school,department,duty,identity')
            ->order('id desc')
            ->find();
        $re= Db::name('school_bind')
            ->where('user_id="'.$id.'" AND is_del=0 AND status<>"2"')
            ->field('school,middle_school,user_type,department,duty,identity,status')
            ->order('id desc')
            ->find();
        //有处理中
        if($re){
            //数据不全补齐特殊处理
            if($re['status']==1 && ($user['department']=='' ||$user['duty']==''||$user['identity']=='')){
                Db::name('zd_user')->where(['user_id'=>$id])->update(['department'=>$re['department'],'duty'=>$re['duty'],'identity'=>$re['identity']]);
            }
            if($re['user_type'] == 1 || $re['user_type'] == 2){
                return [
                    'dealing'=>1,
                    'status'=>$re['status'],
                    'school_name'=>$re['middle_school'],
                    'identity'=>$re['identity']
                ];
            }else{
                return [
                    'dealing'=>1,
                    'status'=>$re['status'],
                    'school_name'=>$re['school'],
                    'identity'=>$re['identity']
                ];
            }
        }
        //无处理中
        else{
            if($user['type'] == 1 || $user['type'] == 2){
                return [
                    'dealing'=>0,
                    'status'=>$user['crowd']==0?0:1,
                    'school_name'=>$user['middle_school'],
                    'identity'=>$user['crowd']==1?'学生':($user['crowd']==2?'家长':'其他')
                ];
            }else{
                return [
                    'dealing'=>0,
                    'status'=>$user['crowd']==0?0:1,
                    'school_name'=>$user['school'],
                    'identity'=>$user['crowd']==1?'学生':($user['crowd']==2?'家长':'其他')
                ];
            }
        }
    }

    //获取会员
    public function get_vip($id){
        //五查会员
        $time=time();
        $where['crowd']=2;//20220530后全部为2
        $where['user_id']=$id;
        $where['is_del']=0;
        $where['vip_type']=2;
        $time_wucha=Db::name('zd_user_vip')
            ->where($where)
            ->order('id desc')
            ->limit(1)
            ->value('end_time');
        if($time_wucha){
            if($time_wucha>$time){
                //生效中
                $data['wucha']['is_vip']=1;
                $data['wucha']['vip_time']=$time_wucha;
                if(($time_wucha-$time)>15552000){
                    $data['wucha']['vip_time_length']='年';
                }else{
                    $data['wucha']['vip_time_length']='月';
                }
                $data['wucha']['vip_time_type']='已开通';
            }else{
                //过期
                $data['wucha']['is_vip']=0;
                $data['wucha']['vip_time_type']='已过期';
                //$data['wucha']['vip_time']=ceil(($time-$time_wucha)/(3600*24));
                $data['wucha']['vip_time']=$time_wucha;
                $data['wucha']['vip_time_length']='';
            }
        }else{
            $data['wucha']['is_vip']=0;
            $data['wucha']['vip_time_type']='未开通';
            $data['wucha']['vip_time']=0;
            $data['wucha']['vip_time_length']='';
        }
        unset($where['crowd']);

        //填报会员
        $where['vip_type']=3;
        $time_tianbao=Db::name('zd_user_vip')
            ->where($where)
            ->order('id desc')
            ->limit(1)
            ->value('end_time');
        if($time_tianbao){
            if($time_tianbao>$time){
                $data['tianbao']['is_vip']=1;
                $data['tianbao']['vip_time_type']='已开通';
            }else{
                $data['tianbao']['is_vip']=0;
                $data['tianbao']['vip_time_type']='已过期';
            }
            $data['tianbao']['vip_time']=$time_tianbao;
        }else{
            $data['tianbao']['vip_time_type']='未开通';
            $data['tianbao']['is_vip']=0;
            $data['tianbao']['vip_time']=0;
        }
        //陪伴会员
        $where=[];
        //$where['a.crowd']=$this->crowd;
        $where['a.user_id']=$id;
        $where['a.is_del']=0;
        $where['a.end_time']=['gt',$time];
        $where['a.vip_type']=4;
        $teacher_vip=Db::name('zd_user_vip')
            ->alias('a')
            ->join('app_zd_teacher_vip b','a.teacher_id=b.id','left')
            ->field('b.teacher_head_image,b.teacher_name,b.id teacher_id')
            ->where($where)
            ->order('a.id desc')
            ->select();
        //$data['sql']=Db::name('zd_user_vip')->getLastSql();
        $data['teacher_vip_list']=$teacher_vip;
        $teacher_vip_num=count($teacher_vip);
        if($teacher_vip_num){
            $time_tianbao=Db::name('zd_user_vip')
                ->alias('a')
                ->where($where)
                ->order('a.id desc')
                ->limit(1)
                ->value('a.end_time');
            if($time_tianbao>$time){
                $data['peiban']['vip_time_type']='已开通';
                $data['peiban']['is_vip']=1;
            }else{
                $data['peiban']['vip_time_type']='已过期';
                $data['peiban']['is_vip']=0;
            }
            $data['peiban']['vip_num']=$teacher_vip_num;
            $data['peiban']['vip_time']=$time_tianbao;
        }else{
            $data['peiban']['vip_time_type']='未开通';
            $data['peiban']['is_vip']=0;
            $data['peiban']['vip_num']=0;
            $data['peiban']['vip_time']=0;
        }
        return $data;
    }
}
